package com.dingding.facde.activity.dto.sql;

/**
 * 
 * @ClassName: CouponSQL 
 * @Description: 用户优惠券相关SQL
 * 注意：用户优惠券与优惠券不是同一张表
 * @author CHEHAUNBO 
 * @date 2016年2月17日 下午2:56:03
 * @since v1.8.4 
 * 
 */
public class UserCouponSQL {

	/**
	 * 根据ID获取用户的所有优惠券
	 * @deprecated
	 * @author CHEHUANBO
	 */
	public static final String GET_USER_COUPON_SQL = "SELECT UP.ucId,UP.couponStatus,UP.expiredTime,CP.couponType,UP.couponMoney,CP.usageRule FROM usercoupon UP INNER JOIN coupon CP ON CP.cpId = UP.couponId WHERE UP.couponStatus != 3 AND UP.loginId = ?";
	
	/**
	 * 根据ID获取用户的所有优惠券
	 * @author YHQ
	 */
	public static final String GET_USER_COUPON_LIST_SQL = "SELECT UP.ucId AS userCouponId, UP.couponStatus,UP.expiredTime,CP.couponTypeId AS couponType,UP.couponMoney,CP.usageRule,UP.couponDiscounts,UP.couponImage,CP.couponName FROM usercoupon UP INNER JOIN dd_coupon CP ON CP.couponId = UP.couponId WHERE UP.couponStatus != 3 AND UP.loginId = ?";

	/**
	 * 根据用户ID获取优惠券详情
	 * @author CHEHUANBO
	 * 
	 */
	public static final String GET_USER_COUPONINFO_SQL = "SELECT uc.ucId,uc.couponMoney,cp.useRule,uc.expiredTime FROM usercoupon uc INNER JOIN coupon cp ON cp.cpId = uc.couponId WHERE uc.ucId = ?";

	/**
	 * 根据用户优惠券ID激活优惠券
	 * @author CHEHUANBO	
	 */
	public static final String AVTIVITE_COUPON_SQL = "UPDATE usercoupon UP SET up.couponStatus = 2 WHERE UP.ucId = ?";

	/**
	 * 根据用户优惠券ID获取门店使用优惠券信息
	 * @author YHQ
	 */
	public static final String GET_USE_COUPON_INFO = "SELECT c.couponName,ud.tellphone,up.expiredTime,up.couponStatus,v.vehicleModel FROM usercoupon up INNER JOIN coupon c ON up.couponId = c.cpId INNER JOIN userdetail ud ON up.loginId = ud.loginId INNER JOIN vehicleinfo v ON ud.loginId = v.loginId WHERE up.ucId = ?";

	/**
	 * 使用优惠券
	 * @author fengshuonan 
	 */
	public static final String USE_COUPON = "UPDATE usercoupon UP SET UP.couponStatus = 3 WHERE UP.ucId = ?";

	/**
	 * 微信激活所有未激活的优惠券
	 * @author 冯硕楠
	 */
	public static final String WECHAT_AVTIVITE_ALL_COUPON_SQL = "UPDATE usercoupon UP INNER JOIN userlogin UL ON UL.uid = UP.loginId INNER JOIN coupon CP ON CP.cpId = UP.couponId SET UP.couponStatus = 2 WHERE UL.userName = ? AND UP.couponStatus = 1";
}
